package com.czf.testmodule.service;

import com.alibaba.fastjson.JSON;
import com.czf.testmodule.dymamicConnect.CzfDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * @author zfChen
 * @create 2022/3/9 15:53
 */
@Slf4j
@Service
public class UpLoadService {

    @Autowired
    private CzfDataSource dataSource;

    /**
     * 从文件中扫描得到insert的sql语句
     * @return
     */
    public List<String> getSql(MultipartFile file) {

        try {
            InputStream stream = file.getInputStream();
            // 字节流转换成字符流
            BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(stream));
            StringBuilder str = new StringBuilder();
            String line = null;
            List<String> stringList = new ArrayList<>();
            //按行读取
            while ((line = bufferedReader.readLine()) != null) {
                // 获取insert语句
                if (line.startsWith("INSERT") || line.startsWith("insert")) {
                    while (line != null) {
                        if (line.endsWith(";")) {
                            str.append(line);
                            stringList.add(str.toString());
                            str.delete(0, str.length());
                            break;
                        }
                        if (line == null) {
                            continue;
                        }
                        str.append(line);
                        line = bufferedReader.readLine();
                    }
                }
            }
            bufferedReader.close();

            return stringList;

        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 得到一个key为字段value为参数值的map
     *
     * @param sql
     * @return
     */
    public HashMap<String, Object> getInsertMap(String sql) {

        //获得字段
        List<String> key = getKeys(sql);
        log.info("key = {}", JSON.toJSONString(key));

        //获得字段值
        List<String> value = getValues(sql);
        log.info("value={}", JSON.toJSONString(value));


        //组合成一个map
        HashMap<String, Object> map = new HashMap<>();
        try {
            for (int i = 0; i < value.size(); i++) {
                if (i == 0) {
                    // 将表名放入map中
                    map.put("table", key.get(i));
                    continue;
                }
                map.put(key.get(i + 1), value.get(i));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        // 去除随机生成的param_id,time等信息
        map.remove("param_id");
        map.remove("create_time");
        map.remove("modify_time");
        map.remove("creator");
        map.remove("editor");
        map.remove("last_modified_time");
        map.remove("param_desc");
        ArrayList<String> delete = new ArrayList<>();

        // 去除字段值为NULL和null还有为空的字段
        for (String strKey : map.keySet()) {
            if (map.get(strKey).equals("NULL") || map.get(strKey).equals("") || map.get(strKey).equals("null")) {
                delete.add(strKey);
            }
        }
        //删除值为“NULL”的字段和值
        delete.stream().forEach(item -> map.remove(item));

        log.info("map={}", JSON.toJSONString(map));
        return map;
    }

    /**
     * 拼接sql语句
     *
     * @param map
     * @return
     */
    public Map<String, Object> splitSql(HashMap<String, Object> map) {
        String table = (String) map.get("table");
        map.remove("table");
        StringBuilder builder = new StringBuilder();
        for (String key : map.keySet()) {
            builder.append(key + " = " + "'" + map.get(key) + "'" + " and ");
        }
        // 去除最后的”and“
        builder.delete(builder.length() - 4, builder.length());
        String sql = "select * from " + table + " where " + builder;
        log.info("拼接的sql为 = {}", sql);

        try {
            // 执行sql
            Map<String, Object> queryForMap = dataSource.jdbcTemplate.queryForMap(sql);
            log.info("通过jdbcTemplate得到map={}", JSON.toJSONString(queryForMap));
            return queryForMap;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 获取insert语句中的key值
     * @param str
     * @return
     */
    public List<String> getKeys(String str) {
        ArrayList<String> key = new ArrayList<>();
        String regex = "`(\\w+)`(?!\\.)";
        Pattern compile = Pattern.compile(regex);
        Matcher matcher = compile.matcher(str);
        while (matcher.find()) {
            key.add(matcher.group(1).toString());  //group(1):表示第一个（）里的内容
        }

        // 如果表名和字段名没有反单引号，采取下列措施
        if (CollectionUtils.isEmpty(key)) {
            Pattern compileTable = Pattern.compile("(to|TO) (\\w+)");
            Matcher matcherTable = compileTable.matcher(str);
            if (matcherTable.find()) {
                key.add(matcherTable.group(2).toString());
            }
            Pattern compile1 = Pattern.compile("(\\()(.+)(\\))( ?)(V|v)");
            Matcher matcher1 = compile1.matcher(str);
            while (matcher1.find()) {
                String s = matcher1.group(2).toString();
                Arrays.stream(s.replace(" ", "").split(",")).forEach(item -> key.add(item));
            }
        }

        return key;
    }

    /**
     * 获取insert语句中的value值
     * @param str
     * @return
     */
    public List<String> getValues(String str) {
        //获得字段值
        String string = "";
        if ("".equals(string)) {
            String regex2 = "(values ?|VALUES ?)(\\()(.+)(\\))";
            Pattern compile2 = Pattern.compile(regex2);
            Matcher matcher2 = compile2.matcher(str);
            while (matcher2.find()) {
                string = matcher2.group(3).toString();
            }
        }

        List<String> list = new ArrayList<>();
        log.info("str的长度={}",string.length());

        String concatStr = "";

        for (int i = 0; i < string.length(); i++) {
            char c = string.charAt(i);

            // 针对括号里的内容进行获取
            if(string.charAt(i) == '(') {
                int num = 0;
                for (int j = i; j < string.length(); j++) {
                    if (string.charAt(j) == '(') {
                        num++;
                    }
                    if (string.charAt(j) == ')') {
                        concatStr += string.charAt(j);
                        num --;
                        if (num == 0) {
                            i = j+1;
                            break;
                        }
                        continue;
                    }
                    concatStr += string.charAt(j);
                }
            }

            // 针对单引号里的内容进行获取
            if (c == '\'' || c == '\"') {
                if (string.charAt(i+1) == '\'' || string.charAt(i+1) == '\"') {
                    i++;
                    continue;
                }
                for (int j = i+1; j < string.length(); j++) {
                    char charAt = string.charAt(j);
                    if (charAt == '\'' || charAt == '\"') {
                        i = j+1;
                        break;
                    }
                    concatStr += charAt;
                }
            }

            if(i >= string.length()){
                break;
            }

            if (i == string.length()-1) {
                if (string.charAt(i) == '\'' || c == '\"') {
                    break;
                }
                else {
                    concatStr += c;
                    break;
                }
            }

            if (string.charAt(i) == ',') {
                list.add(concatStr);
                concatStr = "";
                continue;
            }

            if (c == ' '){
                continue;
            }


            concatStr += string.charAt(i);
        }
        list.add(concatStr);
        return list;
    }
}
